﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Sql;
using MySql.Data.MySqlClient;
using Model;

namespace DAO_Layer
{
    class MySqlDaoTipoTrabajador : DAO_TipoTrabajador
    {

        private String TableName = "TIPO_TRABAJADOR";
        Sqlconnector mysqlconn;

        public MySqlDaoTipoTrabajador()
        {
            mysqlconn = new Sqlconnector();
        }

        public void add(Model.TipoTrabajador p)
        {
            mysqlconn.Open();
            
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = mysqlconn.Mysqlconnection;
            comm.CommandText =
                    "INSERT INTO "+TableName+"(NOMBRE) " +
                    "VALUES (@Param1)";

            MySqlParameter param1 = new MySqlParameter("@Param1", MySqlDbType.VarChar, 50);
            param1.Value = p.Nombre;
            
            comm.Parameters.Add(param1);

            comm.ExecuteNonQuery();
            
            
            mysqlconn.Close();


        }

        public void update(Model.TipoTrabajador p)
        {
            throw new NotImplementedException();
        }

        public void delete(int id)
        {
            mysqlconn.Open();

            MySqlCommand comm = new MySqlCommand();
            comm.Connection = mysqlconn.Mysqlconnection;
            comm.CommandText =
                    "DELETE FROM  " + TableName + " WHERE ID =@Param1";

           
            MySqlParameter param1 = new MySqlParameter("@Param1", MySqlDbType.Int32);
            param1.Value = id;
            comm.Parameters.Add(param1);


            comm.ExecuteNonQuery();
            mysqlconn.Close();
        }

        public List<Model.TipoTrabajador> queryAll()
        {
            List<TipoTrabajador> list = new List<TipoTrabajador>();

            mysqlconn.Open();
            
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = mysqlconn.Mysqlconnection;
            comm.CommandText = "SELECT * FROM " + TableName;
            MySqlDataReader myReader = null;

            try
            {
                myReader = comm.ExecuteReader();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

            while (myReader.Read())
            {
           
                TipoTrabajador p = new TipoTrabajador();
                p.Id = Int32.Parse(myReader["ID"].ToString());
                p.Nombre = myReader["NOMBRE"].ToString();                
                list.Add(p);
            }
            
            myReader.Close();
            mysqlconn.Close();

            return list;

        }

        public TipoTrabajador queryById(int id)
        {
            TipoTrabajador p = new TipoTrabajador();
            
            mysqlconn.Open();

            MySqlCommand comm = new MySqlCommand();
            comm.Connection = mysqlconn.Mysqlconnection;
            comm.CommandText = "SELECT * FROM " + TableName+ " WHERE ID =@Param1";
            MySqlParameter param1 = new MySqlParameter("@Param1", MySqlDbType.Int32);
            param1.Value = id;
            comm.Parameters.Add(param1);

            MySqlDataReader myReader = null;

            try
            {
                myReader = comm.ExecuteReader();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }


            if (myReader.Read())
            {
        
                p.Id = Int32.Parse(myReader["ID"].ToString());
                p.Nombre = myReader["NOMBRE"].ToString();
     
            }
            myReader.Close();
            mysqlconn.Close();

            return p;
        }
    }
}
